import os
from datetime import datetime
import pandas as pd
import json
import requests


# 1. 安装postman，测试添加电影类型接⼝是否正确
# 2. 创建以⽇期为时间为名字的⽇志⽂件，格式如20230303102030.log
# log_filename=datetime.now().strftime("%Y%m%d%H%M%S") + ".log"
# os.system(f"touch /root/homeworkarchive/python1/0609/{log_filename}")

# 3. 列表[["Google",10],["Runoob",12],["Wiki",13]],转成DataFrame数据打印
# data=[["Google",10],["Runoob",12],["Wiki",13]]
# df=pd.DataFrame(data,columns=['Site','Age'])
# print(df.to_string(index=False))

# 4. 将[{"a": 1, "b": 2},{"a": 5, "b": 10, "c": 20}]转成DataFrame数据，提取第2⾏a那⼀列的
# 数据
# data=[{"a": 1, "b": 2},{"a": 5, "b": 10, "c": 20}]
# df=pd.DataFrame(data)
# print(df.to_string(index=False))
# print("-"*50)
# print(df["a"][1])

# 5. 将data = {"chinese": [89,97,68,56,88,77],"math": [99,67,100,78,89,66],"english":
# [73,57,89,90,82,55]}转成DataFrame数据,提取三科分数都及格的数据
# data = {"chinese": [89,97,68,56,88,77],"math": [99,67,100,78,89,66],"english":[73,57,89,90,82,55]}
# df=pd.DataFrame(data)
# print(df.to_string(index=False))
# print("-"*50)
# print(df[(df.chinese>60) & (df.math>60) & (df.english>60)].to_string(index=False))

# 6. 使⽤pandas处理douban.txt，提取列id,title,rate,并且提取rate⼤于7.5的⾏导出douban1.csv
# content=""  #初始化一个字符串变量
# with open("/root/homeworkarchive/python1/0609/douban.txt",mode="r") as f : #打开douban.txt文件并重命名为f
#     content=f.read() #调用文件对象f的read()方法，将整个文件内容读取到字符串变量content中
#     f.close() #关闭文件
# content=json.loads(content) #使用json.loads()函数将字符串content解析为json对象
# df=pd.DataFrame(content["subjects"]) #从json对象中取出键为"subjects"的值，该值应该是一个列表，其中每个元素代表一个电影条目（字典形式）。使用这个列表创建一个 pandas DataFrame，并赋值给变量df
# df=df[(df.rate.astype(float)>7.5)][["id","title","rate"]] #读取rate>7.5的行(先将rate转为float，之前是字符串)，并且输出的只有id，title，rate三列
# print(df) #写完了打印出来看看效果
# df.to_csv("/root/homeworkarchive/python1/0609/douban.csv",index=False) #导出转化为csv文件，并去掉索引

# 7. 把order.xlsx使⽤mobox传⼊到/root/python中，根据⽂档做如下操作
# 把⽂档数据（从列名开始）转换为dataframe输出
# 在产品后⾯增加⼀列采购⼈，内容为 ["坤坤","杰杰","坤坤","丽丽","丽丽","坤坤"]
# 查看⾦额⼤于100的坤坤的订单列表
# 查看⾦额⼤于100的坤坤的订单列表的产品及⾦额列
# 把上⼀步的结果另存为order_kunkun.xlsx，表单名称为坤坤，不显⽰索引，如下：
# 产品 ⾦额
# 投影仪 2000
# 打印机 298

# 1
# df=pd.read_excel("/root/homeworkarchive/python1/0609/order.xlsx",sheet_name="Sheet1",header=1)
# print(df.to_string(index=False))

# 2
# df.insert(2,"采购人",["坤坤","杰杰","坤坤","丽丽","丽丽","坤坤"])
# df["采购人"]=["坤坤","杰杰","坤坤","丽丽","丽丽","坤坤"]
# print(df.to_string(index=False))
# print("-"*50)

# 3
# print(df[(df['金额']>100) & (df['采购人']=='坤坤')])

# 4
# print(df[['产品','金额']][(df['金额']>100) & (df['采购人']=='坤坤')].to_string(index=False))

# 5
# content=(df[['产品','金额']][(df['金额']>100) & (df['采购人']=='坤坤')])
# content.to_excel("/root/homeworkarchive/python1/0609/order_kunkun.xlsx",sheet_name="坤坤",index=False)



# 喜剧类
# url1="https://movie.douban.com/j/chart/top_list"
# params1={"type":24,"interval_id":"100:90","action":"","start":0,"limit":711}
# header1={"user-agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/116.0.5845.97 Safari/537.36 Core/1.116.520.400 QQBrowser/19.2.6473.400"}
# result=requests.get(url=url1,params=params1,headers=header1)
# content=result.json()
# df=pd.DataFrame(content)
# df=df[["id","title","release_date","score"]]
# df["tid"]=1
# df.to_csv("/root/homeworkarchive/python1/0609/doubanMovie.csv",index=False)

# 动作类
# url1="https://movie.douban.com/j/chart/top_list"
# params1={"type":5,"interval_id":"100:90","action":"","start":0,"limit":453}
# header1={"user-agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/116.0.5845.97 Safari/537.36 Core/1.116.520.400 QQBrowser/19.2.6473.400"}
# result=requests.get(url=url1,params=params1,headers=header1)
# content=result.json()
# df=pd.DataFrame(content)
# df=df[["id","title","release_date","score"]]
# df["tid"]=2
# df.to_csv("/root/homeworkarchive/python1/0609/doubanMovie.csv",index=False,mode="a",header=False)

# 科幻类
# url1="https://movie.douban.com/j/chart/top_list"
# params1={"type":17,"interval_id":"100:90","action":"","start":0,"limit":230}
# header1={"user-agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/116.0.5845.97 Safari/537.36 Core/1.116.520.400 QQBrowser/19.2.6473.400"}
# result=requests.get(url=url1,params=params1,headers=header1)
# content=result.json()
# df=pd.DataFrame(content)
# df=df[["id","title","release_date","score"]]
# df["tid"]=3
# df.to_csv("/root/homeworkarchive/python1/0609/doubanMovie.csv",index=False,mode="a",header=False)

# 动画类
# url1="https://movie.douban.com/j/chart/top_list"
# params1={"type":25,"interval_id":"100:90","action":"","start":0,"limit":249}
# header1={"user-agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/116.0.5845.97 Safari/537.36 Core/1.116.520.400 QQBrowser/19.2.6473.400"}
# result=requests.get(url=url1,params=params1,headers=header1)
# content=result.json()
# df=pd.DataFrame(content)
# df=df[["id","title","release_date","score"]]
# df["tid"]=4
# df.to_csv("/root/homeworkarchive/python1/0609/doubanMovie.csv",index=False,mode="a",header=False)

# 恐怖类
# url1="https://movie.douban.com/j/chart/top_list"
# params1={"type":20,"interval_id":"100:90","action":"","start":0,"limit":336}
# header1={"user-agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/116.0.5845.97 Safari/537.36 Core/1.116.520.400 QQBrowser/19.2.6473.400"}
# result=requests.get(url=url1,params=params1,headers=header1)
# content=result.json()
# df=pd.DataFrame(content)
# df=df[["id","title","release_date","score"]]
# df["tid"]=5
# df.to_csv("/root/homeworkarchive/python1/0609/doubanMovie.csv",index=False,mode="a",header=False)

# 去掉位于列表后面的重复数据
# df=pd.read_csv("/root/homeworkarchive/python1/0609/doubanMovie.csv")
# df=df.drop_duplicates(subset=["id","title","release_date","score"])
# df.to_csv("/root/homeworkarchive/python1/0609/doubanMovie1.csv",index=False)

# 将爬取的数据导入数据库中
# os.system("cp /root/homeworkarchive/python1/0609/doubanMovie1.csv /usr/local/mysql/data/doubanMovie1.csv")
# os.system("/root/shell/mysqlcsv.sh Movie /usr/local/mysql/data/doubanMovie1.csv")
